<?php
/**
 * Created by PhpStorm.
 * User: kc
 * Date: 16/11/23
 * Time: 下午4:50
 */
class ScsOperational{

    public static function executeMoneySql($shop_id,$b_time,$e_time){
        $result = DB::select('select (
            ifnull((select sum(opr.amount) as value from orders o LEFT JOIN order_pay_record opr on opr.order_id=o.order_id  
            where o.shop_id = "' . $shop_id . '" and  opr.`status`=1 and (o.pay_time between "' . $b_time . '" and "' . $e_time . '") and 
            o.status in (1018,1017,1217,1317,1417) and opr.pay_type in(1,2,3,4,5,6,7,8,11)),0)
             + 
             ifnull((select sum(o.pay_amount) as value from car_wash_order o where o.shop_id = "' . $shop_id . '" and 
            (o.pay_time between "' . $b_time . '" and "' . $e_time . '") and o.status = 1417),0)
             + 
            ifnull((select sum(opr.amount) as value from service_card_order o LEFT JOIN service_card_order_pay_record opr on opr.order_id=o.order_id  
            where o.shop_id = "' . $shop_id . '" and  opr.`status`=1 and (o.pay_time between "' . $b_time . '" and "' . $e_time . '") and 
            o.status in (1018,1017,1217,1317,1417) and opr.pay_type in(1,2,3,4,5,6,7,8,11)),0)
             + 
             ifnull((select 0-sum(ror.amount) as value from return_order LEFT JOIN orders on return_order.order_id=orders.order_id 
             left join return_order_record ror on ror.return_order_id=return_order.id  where orders.shop_id="' . $shop_id . '" 
             and return_order.`status`=1 and (return_order.implement_time between "' . $b_time . '" and "' . $e_time . '") and ror.pay_type in(1,2,3,4,5,6,7,8,11)),0) 
             + 
             ifnull((select 0-sum(rwr.amount) as value from return_carwash_order LEFT JOIN car_wash_order on return_carwash_order.order_id=car_wash_order.order_id 
             LEFT JOIN return_washorder_record rwr on rwr.return_washorder_id=return_carwash_order.id where car_wash_order.shop_id="' . $shop_id . '" 
             and return_carwash_order.`status`=1 and (return_carwash_order.implement_time between "' . $b_time . '" and "' . $e_time . '")),0)
             +
             ifnull((select 0-sum(rscr.amount) as value from return_service_card_order LEFT JOIN service_card_order on return_service_card_order.order_id=service_card_order.order_id 
             LEFT JOIN return_service_card_record rscr on rscr.return_order_id=return_service_card_order.id where service_card_order.shop_id="' . $shop_id . '" 
             and return_service_card_order.`status`=1 and (return_service_card_order.implement_time between "' . $b_time . '" and "' . $e_time . '")),0)
            ) as value;');
        return $result[0]->value?$result[0]->value:0;
    }
    public static function executePayTypeSql($shop_id,$b_time,$e_time){
        $arr = array();
        $opr = DB::select('select sum(op.amount) as value,op.pay_type from orders o left join order_pay_record op on op.order_id=o.order_id 
            where op.status=1 and o.shop_id = "' . $shop_id . '" and o.place=2 
            and (o.pay_time between "' . $b_time . '" and "' . $e_time . '") and o.status in (1018,1017,1217,1317,1417) group by op.pay_type;');
        $arr = ScsOperational::getPayArr($arr,$opr);
        $opr1 = DB::select('select sum(op.amount) as value,op.pay_type from car_wash_order o left join car_wash_order_pay_record op on op.order_id=o.order_id 
            where op.status=1 and o.shop_id = "' . $shop_id . '"
            and (o.pay_time between "' . $b_time . '" and "' . $e_time . '") and o.status in (1417) group by op.pay_type;');
        $arr = ScsOperational::getPayArr($arr,$opr1);
        $opr2 = DB::select('select sum(0 - ror.amount) as value,ror.pay_type from return_order ro left join orders o on o.order_id = ro.order_id 
            left join return_order_record ror on ror.return_order_id=ro.id where o.shop_id = "' . $shop_id . '" and o.place=2
            and ro.status=1 and (ro.implement_time between "' . $b_time . '" and "' . $e_time . '") group by ror.pay_type;');
        $arr = ScsOperational::getPayArr($arr,$opr2);
        $opr3 = DB::select('select sum(0 - rwr.amount) as value,rwr.pay_type from return_carwash_order ro left join car_wash_order o on o.order_id = ro.order_id 
            left join return_washorder_record rwr on rwr.return_washorder_id=ro.id where o.shop_id = "' . $shop_id . '"
        and ro.status=1 and (ro.implement_time between "' . $b_time . '" and "' . $e_time . '") group by rwr.pay_type;');
        $arr = ScsOperational::getPayArr($arr,$opr3);
        $opr4 = DB::select('select sum(op.amount) as value,op.pay_type from service_card_order o left join service_card_order_pay_record op on op.order_id=o.order_id
            where op.status=1 and o.shop_id = "' . $shop_id . '"
            and (o.pay_time between "' . $b_time . '" and "' . $e_time . '") and o.status in (1018,1017,1217,1317,1417) group by op.pay_type;');
        $arr = ScsOperational::getPayArr($arr,$opr4);
        $opr4 = DB::select('select sum(0 - rscr.amount) as value,rscr.pay_type from return_service_card_order LEFT JOIN service_card_order on return_service_card_order.order_id=service_card_order.order_id 
             LEFT JOIN return_service_card_record rscr on rscr.return_order_id=return_service_card_order.id where service_card_order.shop_id="' . $shop_id . '"
             and return_service_card_order.`status`=1 and  (return_service_card_order.implement_time between "' . $b_time . '" and "' . $e_time . '") and service_card_order.status in (1018,1017,1217,1317,1417) group by rscr.pay_type;');
        $arr = ScsOperational::getPayArr($arr,$opr4);
        return $arr;
    }
    public static function getPayName($arr){
        $arr_1 = array();
        $paytype = Orders::$paytype;
//        $paytype =array(
//            "0"=>"未支付",
//            "1"=>"线下支付现金",
//            "2"=>"支付宝支付",
//            "3"=>"微信支付",
//            "4"=>"大众点评",
//            "5"=>"其他",
//            "6"=>"线下支付挂单",
//            "7"=>"线下支付刷卡",
//            "8"=>"余额支付",
//            "9"=>"复合结算",
//            "10"=>"不记账结算",
//            '49'=>'洗车卡支付',
//            '50'=>'线上洗车卡支付',
//            '69'=>'免费支付',
//            '11'=>'线下支票',
//            '12'=>'划拨',
//            '13'=>'保险',
//            '14'=>'调整充值',
//            '15'=>'银行汇款',
//            '16'=>'百度钱包',
//            '17'=>'易付宝',
//            '18'=>'京东钱包',
//            '19'=>'QQ钱包',
//            '20'=>'智能pos',
//        );
        foreach($arr as $key=>$val){
            $arr_1[$key] = array(
                'name'=>$paytype[$key],
                'money'=>$val / 100
            );
        }
        return $arr_1;
    }
    public static function getCarStationCount($result,$shop_id,$b_time,$e_time){
        $n_b_time = ScsOperational::getTimeDiff($b_time);
        $n_e_time = ScsOperational::getTimeDiff($e_time);

        $opr = DB::select('select count(DISTINCT car_no) as value from orders where shop_id= "' . $shop_id . '" 
              and start_time >=DATE_ADD("' . ScsOperational::getTimeYMD($b_time) . '",INTERVAL - 8 hour) and start_time <DATE_ADD("' . ScsOperational::getTimeYMD($e_time) . '",INTERVAL + 16 hour) ');
        $result['into_num'] = $opr[0]->value?$opr[0]->value:0;
        $opr1 = DB::select('select count(DISTINCT car_no) as value from orders where shop_id= "' . $shop_id . '" and (pay_time between "' . $n_b_time . '" and "' . $n_e_time . '") ;');
        $result['out_num'] = $opr1[0]->value?$opr1[0]->value:0;
        $opr2 = DB::select('select count(DISTINCT car_no) as value from orders where shop_id= "' . $shop_id . '"   and  status in (1215,1216,1412,1416);');
        $result['serv_num'] = $opr2[0]->value?$opr2[0]->value:0;

        return $result;
    }
    public static function getUserCount($result,$shop_id,$b_time,$e_time){               
        // $befor_car_no=$day_array=array();   
        // //当天之前来过的车牌号
        // $order_car_no=DB::select('SELECT DISTINCT car_no FROM orders WHERE pay_time < "'.$b_time.'"  AND STATUS IN (1018,1017,1217,1317,1417)');        
        // foreach ($order_car_no as $ocn) {
        //     $befor_car_no[]=$ocn->car_no;
        // }        
        // unset($order_car_no);
        // //king car 车牌号
        // $kingcar_car=DB::select("SELECT car_no from kingcar_car");
        
        // foreach ($kingcar_car as $kc) {
        //     $befor_car_no[]=$kc->car_no;
        // }
        // unset($kingcar_car);
        // $befor_car_no=array_unique($befor_car_no);
        
        //当天进行的车辆 
        $day_order_car_no=DB::select('select distinct car_no from orders where shop_id="' . $shop_id . '"  
              and (pay_time between "' . $b_time . '" and "' . $e_time . '") and status in (1018,1017,1217,1317,1417)');
        $old_num=$day_number=0;
        foreach ($day_order_car_no as $_dc) {
            $day_number++;
            $o=Orders::whereIn('status',array(1018,1017,1217,1317,1417))->where("pay_time","<",$b_time)->where('order_type',1)
            ->where("car_no",$_dc->car_no)->first();
            $k=KingCar::where("car_no",$_dc->car_no)->first();
            if($o||$k){//如果存在说明是老订单 
                $old_num++;
            }
        }        
        unset($day_order_car_no);
        // $day_number=sizeof($day_array);
        // $old_num=sizeof(array_intersect($befor_car_no,$day_array));        
        // unset($befor_car_no);
        // unset($day_array);        
        $result['old_c_num'] = $old_num;
        $result['new_c_num'] = $day_number-$old_num;
        return $result;
    }
    public static function getOrderCount($result,$shop_id,$b_time,$e_time){
        $n_b_time = ScsOperational::getTimeDiff($b_time);
        $n_e_time = ScsOperational::getTimeDiff($e_time);
        $opr = DB::select('select count(*) as value from orders o where o.shop_id = "' . $shop_id . '" and (o.pay_time between "' . $n_b_time . '" and "' . $n_e_time . '") 
                and o.status in (1018,1017,1217,1317,1417) and o.place = 2');
        $result['close_o_num'] = $opr[0]->value?$opr[0]->value:0;
        $opr1 = DB::select('select count(*) as value from orders o where o.shop_id = "' . $shop_id . '" and o.status in (1216,1416) and o.place = 2 ; ');
        $result['not_close_o_num'] = $opr1[0]->value?$opr1[0]->value:0;
        $opr1 = DB::select('select count(*) as value from orders o where o.shop_id = "' . $shop_id . '" and o.status in (1215) and o.place = 2 ; ');
        $result['finish_not_close_o_num'] = $opr1[0]->value?$opr1[0]->value:0;
        return $result;
    }
    public static function getSupplys($shop_id,$this_b_time,$this_e_time){
        $supply_items = array();
        $supply_items = ScsOperational::getSupplyCount($supply_items,ScsOperational::getItemLevelUp(Report::get_service_order_out_data_for_jxc($shop_id,0,$this_b_time,$this_e_time)));
        $supply_items = ScsOperational::getSupplyCount($supply_items,ScsOperational::getItemLevelUp(Report::get_sale_order_out_data_for_jxc($shop_id,0,$this_b_time,$this_e_time)));
        $supply_items = ScsOperational::getSupplyCount($supply_items,ScsOperational::getItemLevelUp(Report::get_transfer_out_data_for_jxc_ly($shop_id,0,$this_b_time,$this_e_time)));
        $supply_items = ScsOperational::getSupplyCount($supply_items,ScsOperational::getItemLevelUp(Report::get_transfer_out_data_for_jxc_dc($shop_id,0,$this_b_time,$this_e_time)));
        $supply_items = ScsOperational::getSupplyCount($supply_items,ScsOperational::getItemLevelUp(Report::get_transfer_out_data_for_jxc_xs($shop_id,0,$this_b_time,$this_e_time)));
        return $supply_items;
    }
    public static function getSupplyCount($supply_items,$items){
        foreach ($items as $key=>$val){
            $level_1_id = $val['level_1_id'];
            if($level_1_id) {
                if (isset($supply_items[$level_1_id])) {
                    if (isset($supply_items[$level_1_id]['count'])) {
                        $supply_items[$level_1_id]['count'] += $val['number'];
                    } else {
                        $supply_items[$level_1_id]['count'] = $val['number'];
                    }
                } else {
                    $supply_items[$level_1_id]['name'] = $val['level_1_name'];
                    $supply_items[$level_1_id]['id'] = $val['level_1_id'];
                    $supply_items[$level_1_id]['count'] = $val['number'];
                }
            }
        }
        return $supply_items;
    }
    public static function getItemLevelUp($items){
        foreach ($items as &$val){
            // if($val['item_type'] == 0){
                $item_info=SupplyItem::getCacheItemInfo($val['item_id']);
            // }else{
            //     $item_info=SupplyItemNonStandard::getCacheItemInfo($val['item_id']);
            // }
            $part_classification = Report::get_part_classification($item_info->st_classifi_id);
            if($part_classification) {
                $val['level_1_id'] = $part_classification['level_1_id'];
                $val['level_1_name'] = $part_classification['level_1_name'];
            }
        }
        return $items;
    }
    public static function getRatio($array){
        $all_sum = 0;
        foreach($array as $val){
            $all_sum += $val['count'];
        }
        foreach($array as &$val1){
            if($all_sum != 0) {
                $val1['ratio'] = bcmul($val1['count'] / $all_sum, 100, 2);
            }else{
                $val1['ratio'] = 0;
            }
        }
        return $array;
    }
    public static function getServiceName($arr){
        foreach($arr as $key=>$val){
            unset($arr[$key]['order_ids']);
            $arr[$key]['name'] = ServiceClassification::find($key)->name;
        }
        return $arr;
    }
    public static function sortArrByOneField(&$array, $field, $desc = false){
        $fieldArr = array();
        foreach ($array as $k => $v) {
            $fieldArr[$k] = $v[$field];
        }
        $sort = $desc == false ? SORT_ASC : SORT_DESC;
        array_multisort($fieldArr, $sort, $array);
        return $array;
    }
    public static function getPayArr($arr,$opr){
        foreach($opr as $val){
            foreach($val as $key=>$va){
                if($key == 'pay_type'){
                    if(isset($arr[$va])) {
                        $arr[$va] += $val->value;
                    }else{
                        $arr[$va] = $val->value;
                    }
                }
            }
        }
        return $arr;
    }
    public static function getTimeDiff($time){
//        $time = date("Y-m-d H:i:s",strtotime($time) + Config::get("config.report_time_diff"));
        return $time;
    }
    public static function getTimeYMD($time){
        $time = date("Y-m-d",strtotime($time));
        return $time;
    }
    //获取门店类型账户的充、撤、退、调
    public static function getShopAccounting($account_type,$shop_id,$time,$pay_type){
        $sql = "SELECT sum(aurr.amount) amount FROM acount_user_recharge_record aurr LEFT JOIN user_account_details uad ON uad.account_no=aurr.account_no 
                   WHERE uad.`acount_type`=? AND aurr.`status` in (1,6) ";
        $sql_arr = array($account_type);
        if($pay_type !== '' ) {
            $sql .= "AND aurr.`pay_type` =? ";
            $sql_arr[] = $pay_type;
        }
        if($shop_id !== '' && $shop_id != '-1' && $shop_id !== '0') {
            $sql .= "AND uad.`shop_id` = ? ";
            $sql_arr[] = $shop_id;
        }
        $sql .= "AND aurr.`created_at` BETWEEN ? AND ?;";
        $sql_arr[] = $time[0];
        $sql_arr[] = $time[1];
        $sql_r = DB::select($sql,$sql_arr);//充、撤、退
        $sql1 = "SELECT sum(aursr.amount) amount FROM  acount_user_recharge_spend_record aursr LEFT JOIN acount_user_recharge_record aurr 
                   ON aursr.aurr_id=aurr.id LEFT JOIN user_account_details uad ON uad.account_no=aurr.account_no 
                   WHERE aursr.`status`=1 AND aursr.`type` NOT IN (1,2,5,7) AND aurr.`status` IN (1,6) AND uad.`acount_type`=? ";
        $sql_arr1 = array($account_type);
        if($pay_type !== '' ) {
            $sql1 .= "AND aurr.`pay_type` =? ";
            $sql_arr1[] = $pay_type;
        }
        if($shop_id !== '' && $shop_id != '-1' && $shop_id !== '0') {
            $sql1 .= "AND uad.`shop_id`=? ";
            $sql_arr1[] = $shop_id;
        }
        $sql1 .= "AND aursr.created_at BETWEEN ? AND ?;";
        $sql_arr1[] = $time[0];
        $sql_arr1[] = $time[1];
        $sql_r1 = DB::select($sql1,$sql_arr1);
        $sql2 = "SELECT sum(aursr.amount) amount FROM  acount_user_recharge_spend_record aursr LEFT JOIN acount_user_recharge_record aurr 
                   ON aursr.aurr_id=aurr.id LEFT JOIN user_account_details uad ON uad.account_no=aurr.account_no 
                   WHERE aursr.`status`=1 AND aursr.`type` =6 AND aurr.`status` IN (1,6) AND uad.`acount_type`=? ";
        $sql_arr2 = array($account_type);
        if($pay_type !== '' ) {
            $sql2 .= "AND aurr.`pay_type` =? ";
            $sql_arr2[] = $pay_type;
        }
        if($shop_id !== '' && $shop_id != '-1' && $shop_id !== '0') {
            $sql2 .= "AND uad.`shop_id`=? ";
            $sql_arr2[] = $shop_id;
        }
        $sql2 .= "AND aursr.created_at BETWEEN ? AND ?;";
        $sql_arr2[] = $time[0];
        $sql_arr2[] = $time[1];
        $sql_r2 = DB::select($sql2,$sql_arr2);
        $amount_r = (isset($sql_r[0]->amount) ? $sql_r[0]->amount : 0) - (isset($sql_r1[0]->amount) ? $sql_r1[0]->amount : 0) - (isset($sql_r2[0]->amount) ? $sql_r2[0]->amount : 0);
        return $amount_r;
    }
    //类型账户支出
    public static function getAccountPay($shop_id,$time,$account_type){
        $sql = "SELECT sum(ja.amount) amount FROM journal_account ja LEFT JOIN user_account_details uad ON uad.account_no=ja.account_no 
                   WHERE uad.`acount_type`=? AND ja.`action` = 1 ";
        $sql_arr = array($account_type);
        if($shop_id !== '' && $shop_id != '-1' && $shop_id !== '0') {
            $sql .= "AND uad.`shop_id` = ? ";
            $sql_arr[] = $shop_id;
        }
        $sql .= "AND ja.`created_at` BETWEEN ? AND ?;";
        $sql_arr[] = $time[0];
        $sql_arr[] = $time[1];
        $sql_r = DB::select($sql,$sql_arr);
        return (isset($sql_r[0]->amount) ? $sql_r[0]->amount : 0);
    }
    //获取出库成本统计
    public static function getOutListCost($shop_id,$begin_date,$end_date){
        $out_list = ReportDateReocrd::getDate($begin_date,$end_date,$shop_id,"get_out_detail");
        $amount = 0;
        if(!empty($out_list)) {
            foreach ($out_list as $_out_list) {
                if($_out_list['type'] == 1 && $_out_list['methed'] == 2) {
                    $amount += ($_out_list['number'] - $_out_list['return_numer']) * $_out_list['tax_purchase_price'];
                }
            }
        }
        return $amount;
    }
    //获取退货金额
    public static function getReturnAmount($time,$shop_id,$pay_type){
        $return_a = ReturnOrderRecord::LeftJoin('return_order','return_order_record.return_order_id','=','return_order.id')
            ->LeftJoin('orders','orders.order_id','=','return_order.order_id');
        if($shop_id !== '' && $shop_id != '-1' && $shop_id !== '0') {
            $return_a = $return_a->where('orders.shop_id', $shop_id);
        }
        $return_a = $return_a->whereBetween('return_order_record.created_at',$time)->where('return_order_record.pay_type',$pay_type)
            ->sum('return_order_record.amount');
        $return_b = ReturnWashOrderRecord::LeftJoin('return_carwash_order','return_washorder_record.return_washorder_id','=','return_carwash_order.id')
            ->LeftJoin('car_wash_order','car_wash_order.order_id','=','return_carwash_order.order_id');
        if($shop_id !== '' && $shop_id != '-1' && $shop_id !== '0') {
            $return_b = $return_b->where('car_wash_order.shop_id', $shop_id);
        }
        $return_b = $return_b->whereBetween('return_washorder_record.created_at',$time)->where('return_washorder_record.pay_type',$pay_type)
            ->sum('return_washorder_record.amount');
        return $return_a + $return_b;
    }
    //处理复合结算
    public static function getMultipleAmount($orders_1,&$result,$op){
        foreach ($orders_1 as $o1) {
            if($op == 'plus'){
                $amount = $o1->amount;
            }else{
                $amount = -$o1->amount;
            }
            if($o1->pay_type == 6){
                if (isset($result['types'][$o1->pay_type]['payable'])) {
                    $result['types'][$o1->pay_type]['payable'] += $amount;
                } else {
                    $result['types'][$o1->pay_type]['payable'] = $amount;
                }
                if (isset($result['types'][$o1->pay_type]['journal'])) {
                    $result['types'][$o1->pay_type]['journal'] += $amount;
                    $result['types'][$o1->pay_type]['subtotal'] += $amount;
                } else {
                    $result['types'][$o1->pay_type]['journal'] = $amount;
                    $result['types'][$o1->pay_type]['subtotal'] = $amount;
                }
                if (isset($result['total']['payable'])) {
                    $result['total']['payable'] += $amount;//总计
                    $result['total']['journal'] += $amount;
                } else {
                    $result['total']['payable'] = $amount;
                    $result['total']['journal'] = $amount;
                }
            }elseif($o1->pay_type == 8){
                if (isset($result['types'][$o1->pay_type]['consume'])) {
                    $result['types'][$o1->pay_type]['consume'] += $amount;
                } else {
                    $result['types'][$o1->pay_type]['consume'] = $amount;
                }
                if (isset($result['types'][$o1->pay_type]['journal'])) {
                    $result['types'][$o1->pay_type]['journal'] += $amount;
                    $result['types'][$o1->pay_type]['subtotal'] += $amount;
                } else {
                    $result['types'][$o1->pay_type]['journal'] = $amount;
                    $result['types'][$o1->pay_type]['subtotal'] = $amount;
                }
                if (isset($result['total']['consume'])) {
                    $result['total']['consume'] += $amount;//总计
                    $result['total']['journal'] += $amount;
                } else {
                    $result['total']['consume'] = $amount;
                    $result['total']['journal'] = $amount;
                }
            }else {
                if (isset($result['types'][$o1->pay_type]['received'])) {
                    $result['types'][$o1->pay_type]['received'] += $amount;
                } else {
                    $result['types'][$o1->pay_type]['received'] = $amount;
                }
                if (isset($result['types'][$o1->pay_type]['journal'])) {
                    $result['types'][$o1->pay_type]['journal'] += $amount;
                    $result['types'][$o1->pay_type]['subtotal'] += $amount;
                } else {
                    $result['types'][$o1->pay_type]['journal'] = $amount;
                    $result['types'][$o1->pay_type]['subtotal'] = $amount;
                }
                if (isset($result['total']['received'])) {
                    $result['total']['received'] += $amount;//总计
                    $result['total']['journal'] += $amount;
                } else {
                    $result['total']['received'] = $amount;
                    $result['total']['journal'] = $amount;
                }
            }
        }
        return $result;
    }
}